from . import ConnPG
import psycopg2
import sys
from sanic.log import logger


class DutyClassTable:

    # 添加一个关系
    async def add_ship(self, duty_id, class_id):  # 添加关系

        # 参数检查


        if not all([duty_id, class_id]):

            return {
                'status': -1,
                'errorCode': 333100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        sql = '''\
        INSERT INTO duty_class(
            duty_id, class_id
            )
        VALUES (
            %s, %s) RETURNING id'''

        try:
            result = await ConnPG().fetchone(
                sql,
                (duty_id, class_id)

            )
        except psycopg2.errors.UniqueViolation as e:
            print(sys.exc_info())


            return {
                'status': -1,
                'errorCode': 333101,
                'errorMsg': str(e),
                'resultBody': None,
            }
        else:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': result,
            }

    async def select_all_ship(self):#查询所有的关系

        sql = "select * from view_duty_class"

        result = await ConnPG().fetchall(sql)

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }


    async def del_ship(self, duty_id, class_id):#查询所有的关系

        if not all([duty_id, class_id]):

            return {
                'status': -1,
                'errorCode': 333100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        # sql = "select * from duty_class"
        sql = "delete from duty_class where duty_id=%s and class_id=%s"


        await ConnPG().execute(sql, (duty_id, class_id))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': '删除成功',
        }

    async def select_all_sku_by_duty_id(self, duty_id):

        if duty_id == None or not duty_id.isdecimal():

            return {
                'status': -1,
                'errorCode': 303107,
                'errorMsg': '却少duty_id参数',
                'resultBody': None
            }

        sql = "select * from view_duty_class where duty_id=%s"

        result_duty_class_list = await ConnPG().fetchall(sql,(duty_id,))

        class_id_list = [i['class_id'] for i in result_duty_class_list]

        class_list_str = ''

        for i in class_id_list:

            class_list_str = class_list_str + f'or parent_id={i} '

        sql_ = "select * from view_sku_info where " + class_list_str[2:]

        result = await ConnPG().fetchall(sql_)

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }

    
    async def select_all_class_by_duty_id(self, duty_id):

        if duty_id == None or not duty_id.isdecimal():

            return {
                'status': -1,
                'errorCode': 303107,
                'errorMsg': '却少duty_id参数',
                'resultBody': None
            }

        sql = "select * from view_duty_class where duty_id=%s"

        result_duty_class_list = await ConnPG().fetchall(sql,(duty_id,))

        class_id_list = [i['class_id'] for i in result_duty_class_list]

        class_list_str = ''

        for i in class_id_list:

            class_list_str = class_list_str + f'or id={i} '

        sql_ = "select * from class where " + class_list_str[2:]

        result = await ConnPG().fetchall(sql_)

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }

    
